package com.example.testall;


import com.google.common.cache.Cache;
import com.google.common.cache.CacheBuilder;
import lombok.extern.slf4j.Slf4j;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.PreparedStatementSetter;
import org.springframework.jdbc.datasource.DriverManagerDataSource;
import org.springframework.util.StringUtils;

import java.io.InputStream;
import java.io.Reader;
import java.math.BigDecimal;
import java.net.URL;
import java.sql.*;
import java.time.Clock;
import java.time.Instant;
import java.time.LocalTime;
import java.time.ZoneId;
import java.util.*;
import java.util.Date;
import java.util.concurrent.TimeUnit;

@Slf4j
public class MysqlDbUtil {
    /**
     * 数据库连接缓存
     */
    private static Cache<String, JdbcTemplate> stringJdbcTemplateCache = CacheBuilder.newBuilder().expireAfterWrite(60 * 10 * 6, TimeUnit.SECONDS).build();
    /**
     * 数据库字段信息缓存  缓存半小时
     */
    private static Cache<String, List<String>> columIndo = CacheBuilder.newBuilder().expireAfterWrite(60 * 30, TimeUnit.SECONDS).build();
    /**
     * 非空字段信息
     */
    private static Cache<String, Map<String,Map<String, Object>>> notnullColumIndo = CacheBuilder.newBuilder().expireAfterWrite(60 * 30, TimeUnit.SECONDS).build();

    private static JdbcTemplate getJdbcTemplate(String url, String user, String psd) {
        JdbcTemplate ifPresent = stringJdbcTemplateCache.getIfPresent(url);
        if (ifPresent != null) {
            return ifPresent;
        } else {
            DriverManagerDataSource dataSource = new DriverManagerDataSource();
            dataSource.setDriverClassName("com.mysql.jdbc.Driver");
            dataSource.setUrl(url);
            dataSource.setUsername(user);
            dataSource.setPassword(psd);
            // 创建JDBC模板
            ifPresent = new JdbcTemplate();
            ifPresent.setDataSource(dataSource);
            stringJdbcTemplateCache.put(url, ifPresent);
            return ifPresent;
        }
    }

    public static void main(String[] args) {
        String url="jdbc:mysql://124.239.180.26:3306/testso?useUnicode=true&characterEncoding=utf8&rewriteBatchedStatements=true&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8 ";
        String user="root";
        String psd="sino@123.com";
        JdbcTemplate jdbcTemplate = getJdbcTemplate(url, user, psd);
        String sql="insert friso_test (`timeS`) values(?)";
          PreparedStatementSetter preparedStatementSetter = new PreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps) throws SQLException {
//                ps.setDate(1,   new java.sql.Date(System.currentTimeMillis()));
                ps.setObject(1,new Date());
            }
        };

        final int update = jdbcTemplate.update(sql,preparedStatementSetter);
        System.out.println(update);
    }


    public static int[] addBatch(List<Object[]> obj, String sql, String url, String userName, String psd) {
        JdbcTemplate jdbcTemplate = getJdbcTemplate(url, userName, psd);

        return jdbcTemplate.batchUpdate(sql, new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement preparedStatement, int i) throws SQLException {
                if (i >= obj.size()) {
                    return;
                }
                Object[] args = obj.get(i);
                for (int i1 = 1; i1 <= args.length; i1++) {
                    preparedStatement.setString(i1, args[i1 - 1] != null ? args[i1 - 1].toString() : null);
                }
            }

            @Override
            public int getBatchSize() {
                return obj.size();
            }
        });

    }

    public static List<Map<String, Object>> queryInfo(String sql, String url, String userName, String psd) {
        JdbcTemplate jdbcTemplate = getJdbcTemplate(url, userName, psd);

        return jdbcTemplate.queryForList(sql);
    }

    public static void batchInsert(List<Map<String, Object>> contactList, String url, String userName, String psd, String tableName) {
        long startTime = System.currentTimeMillis();
        List<String> contactColumList = getColumnInfo(url, userName, psd, tableName);
        final Map<String, Map<String, Object>> notNullColumnInfo = getNotNullColumnInfo(url, userName, psd, tableName);
        //最重要执行的sql
        StringBuffer sql = new StringBuffer("replace into `" + tableName + "`(");
        StringBuffer sqlParam = new StringBuffer();
        StringBuffer valueParam = new StringBuffer();
        List<Object[]> values = new ArrayList<>();
        if(contactList==null||contactList.isEmpty()){
            return;
        }

        for (int i = 0; i < contactColumList.size(); i++) {
                sqlParam.append("`").append(contactColumList.get(i)).append("`").append(",");
                valueParam.append("?,");
        }
        sqlParam = sqlParam.deleteCharAt(sqlParam.lastIndexOf(","));
        for (Map<String, Object> map : contactList) {
            List<Object> list = new ArrayList<>(contactColumList.size());
            for (int i = 0; i < contactColumList.size(); i++) {
                final String key = contactColumList.get(i);
                Object e = map.get(key);
                if (!StringUtils.isEmpty(e) && e.toString().endsWith("+0800")) {
                    e = e.toString().substring(0, e.toString().length() - 5);
                }else if(notNullColumnInfo.containsKey(key)&&e==null){

                    Object columnDefault = notNullColumnInfo.get(key).get("column_default");
                    //默认值是当前时间戳时 赋值成时间类型
                    if(columnDefault!=null&&columnDefault.toString().equals("CURRENT_TIMESTAMP")){
                        e=new Date();
                    }else{
                        e= columnDefault;
                    }

                }
                list.add(e);
            }
            values.add(list.toArray());
        }
        sql.append(sqlParam).append(")");
        if (valueParam != null && valueParam.length() > 0) {
            valueParam.deleteCharAt(valueParam.length() - 1);
        }
        sql.append("VALUES(" + valueParam + ")");
        final int[] addBatch = addBatch(values, sql.toString(), url, userName, psd);
        final long endTime = System.currentTimeMillis();
        log.info("传输数据量{},耗时{}----ms---", values.size(), (endTime - startTime));
    }

    private static List<String> getColumnInfo(String url, String userName, String psd, String tableName) {
        List<String> contactColumList = columIndo.getIfPresent(url + tableName);
        if (contactColumList == null) {

            JdbcTemplate jdbcTemplate = getJdbcTemplate(url, userName, psd);
            String dbname = "";
            final Connection connection;
            try {
                connection = jdbcTemplate.getDataSource().getConnection();
                dbname = connection.getCatalog();
            } catch (SQLException e) {
                e.printStackTrace();
            }

            contactColumList = new ArrayList<>();
            String columnSql = "select column_name  as  COLUMN_NAME  from information_schema.columns where table_name='" + tableName + "'" +
                    "and  table_schema ='" + dbname + "'";
            List<Map<String, Object>> columnList = jdbcTemplate.queryForList(columnSql);
            for (Map<String, Object> map : columnList) {
                final String column_name = map.get("COLUMN_NAME").toString();
                List<String> blackColumn = Arrays.asList(new String[]{});
                if (!blackColumn.contains(column_name)) {
                    contactColumList.add(column_name);
                }
            }
            columIndo.put(url + tableName, contactColumList);
        }
        return contactColumList;
    }
    private static Map<String,Map<String, Object>> getNotNullColumnInfo(String url, String userName, String psd, String tableName) {
        Map<String,Map<String, Object>> contactColumList = notnullColumIndo.getIfPresent(url +"notNull"+ tableName);
        if (contactColumList == null) {
            JdbcTemplate jdbcTemplate = getJdbcTemplate(url, userName, psd);
            String dbname = "";
            final Connection connection;
            try {
                connection = jdbcTemplate.getDataSource().getConnection();
                dbname = connection.getCatalog();
            } catch (SQLException e) {
               log.error("",e);
            }
            contactColumList = new HashMap<>();
            String columnSql = "select column_name  as  COLUMN_NAME,column_default as  COLUMN_DEFAULT from information_schema.columns where table_name='" + tableName + "'" +
                    "and  table_schema ='" + dbname + "' and  is_nullable='NO' and  column_default is not  null";
            List<Map<String, Object>> columnList = jdbcTemplate.queryForList(columnSql);

            for (Map<String, Object> map : columnList) {
                contactColumList.put(map.get("COLUMN_NAME").toString(),map);
            }
            notnullColumIndo.put(url +"notNull"+ tableName, contactColumList);
        }
        return contactColumList;
    }


    private static void update(String sql, String url, String userName, String psd) {
        JdbcTemplate jdbcTemplate = getJdbcTemplate(url, userName, psd);
        jdbcTemplate.update(sql);
    }


}
